﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using DBFile.Core.Interfaces;
using DBFile.Core.Web;
using DBFile.SqlServerProvider;

namespace DBFile.Providers.Impl
{
    public class Sql2008FileStreams : IDBFileProvider
    {
        #region Constants

        // constants for the database columns
        private const int FileName = 0;
        private const int ContentLength = 1;
        private const int MimeType = 2;
        private const int FileStream = 3;
        private const int FileStreamPath = 0;
        private const int FileSteamToken = 1;

        // constants for the SQL to use 
        private const string InsertSql = "ds_insert_file_info";
        private const string SingleFileStreamSelectSql = "ds_get_filestream";
        private const string UpdateSql = "ds_update_file";
        private const string SingleSelectSql = "ds_get_fileinfo";
        private const string SelectSql = "ds_get_file_list";
        private const string DeleteSql = "ds_delete_file";
        private const string SelectCountSql = "ds_get_count";
        private const string CopySql = "ds_copy_file";

        #endregion

        private string connectionString;

        internal Sql2008FileStreams(string connectionString)
        {
            this.connectionString = connectionString;
        }

        private void VerifyCapability(SqlConnection conn)
        {
            if (!SqlServerHelper.IsSql2008(conn))
            {
                throw new ApplicationException("FileStream only available on SQL Server 2008");
            }
        }

        // TODO: move to stored procedure
        public int Copy(string sourceFilename, string destinationFilename, bool overwrite)
        {
            if (!overwrite && Exists(destinationFilename))
            {
                return 0;
            }
            IDBFile file = Get<HttpPostedFile>(sourceFilename);
            return Insert(destinationFilename, file.FileBytes, file.ContentType, file.ContentLength);
        }

        public int Delete(string filename)
        {
            if (filename.Length == 0)
                return 0;

            SqlConnection conn = null;
            int rowsAffected = 0;

            using (conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(DeleteSql, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter p = new SqlParameter("@FileName", SqlDbType.VarChar, 255);
                p.Value = filename;
                cmd.Parameters.Add(p);

                conn.Open();
                rowsAffected = cmd.ExecuteNonQuery();
                conn.Close();
            }
            return rowsAffected;
        }

        public bool Exists(string filename)
        {
            SqlConnection conn = null;
            int existingRows = 0;
            using (conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(SelectCountSql, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter p = new SqlParameter("@FileName", SqlDbType.VarChar, 255);
                p.Value = filename;
                cmd.Parameters.Add(p);

                conn.Open();
                existingRows = (int)cmd.ExecuteScalar();

                conn.Close();
            }
            return (existingRows > 0);
        }

        public T Get<T>(string filename) where T : IDBFile, new()
        {
            T result = new T();

            SqlConnection conn = null;
            using (conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction tx = conn.BeginTransaction();

                // Get the file info
                SqlCommand cmd = new SqlCommand(SingleSelectSql, conn);
                cmd.Transaction = tx;
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter p = new SqlParameter("@FileName", SqlDbType.VarChar, 255);
                p.Value = filename;
                cmd.Parameters.Add(p);

                SqlDataReader rdr = cmd.ExecuteReader();
                // current implementation is only interested in one file, duplicates are not currently handled
                if (rdr.Read())
                {
                    result.FileName = rdr[FileName].ToString();
                    result.ContentLength = rdr.GetInt32(ContentLength);
                    result.ContentType = rdr[MimeType].ToString();
                }
                rdr.Close();

                // Get the file stream
                SqlCommand fileCmd = conn.CreateCommand();
                fileCmd.Transaction = tx;

                fileCmd.CommandText = SingleFileStreamSelectSql;
                fileCmd.CommandType = CommandType.StoredProcedure;
                fileCmd.Parameters.Add("@FileName", SqlDbType.VarChar, 255).Value = filename;

                SqlDataReader reader = fileCmd.ExecuteReader(CommandBehavior.SingleRow);
                reader.Read();

                SqlString streamPath = reader.GetSqlString(FileStreamPath);
                SqlBinary streamToken = reader.GetSqlBinary(FileSteamToken);
                reader.Close();

                SqlFileStream sqlFile = new SqlFileStream(streamPath.Value, streamToken.Value, FileAccess.Read);
                int bufferSize = 2048;
                int readCount = 0;
                int position = 0;

                result.FileBytes = new byte[result.ContentLength];
                readCount = sqlFile.Read(result.FileBytes, 0, bufferSize);
                position += readCount;
                while (readCount > 0)
                {
                    if ((position + bufferSize) > result.ContentLength)
                    {
                        bufferSize = (int)result.ContentLength - position;
                    }
                    readCount = sqlFile.Read(result.FileBytes, position, bufferSize);
                    position += readCount;
                }
                sqlFile.Close();
                tx.Commit();
                conn.Close();
            }
            return result;
        }

        public string[] GetFileNameList()
        {
            List<string> result = new List<string>();
            SqlConnection conn = null;
            using (conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(SelectSql, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    result.Add(dr.GetString(FileName));
                }
                conn.Close();
            }

            return result.ToArray();
        }

        public int Insert(string filename, byte[] buffer, string contentType, long contentLength)
        {
            SqlConnection conn = null;
            int rowsAffected = 0;

            using (conn = new SqlConnection(connectionString))
            {
                conn.Open();

                VerifyCapability(conn);

                SqlTransaction tx = conn.BeginTransaction();

                SqlCommand cmd = new SqlCommand(InsertSql, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Transaction = tx;

                // Add the file info
                cmd.Parameters.Add("@FileName", SqlDbType.VarChar, 255).Value = filename;
                cmd.Parameters.Add("@ContentLength", SqlDbType.Int).Value = contentLength;
                cmd.Parameters.Add("@MimeType", SqlDbType.VarChar, 255).Value = contentType;
                                                
                rowsAffected = cmd.ExecuteNonQuery();

                // Add the filestream
                SqlCommand fileCmd = conn.CreateCommand();
                fileCmd.Transaction = tx;

                fileCmd.CommandText = SingleFileStreamSelectSql;
                fileCmd.CommandType = CommandType.StoredProcedure;
                fileCmd.Parameters.Add("@FileName", SqlDbType.VarChar, 255).Value = filename;

                SqlDataReader reader = fileCmd.ExecuteReader(CommandBehavior.SingleRow);
                reader.Read();
                
                SqlString streamPath = reader.GetSqlString(FileStreamPath);
                SqlBinary streamToken = reader.GetSqlBinary(FileSteamToken);
                reader.Close();

                SqlFileStream sqlFile = new SqlFileStream(streamPath.Value, streamToken.Value, FileAccess.Write);
                                
                sqlFile.Write(buffer, 0, buffer.Length);
                
                sqlFile.Close();
                tx.Commit();
                conn.Close();
            }
            return rowsAffected;
        }

        public int Insert(string filename, byte[] buffer, string contentType, long contentLength, bool overwrite)
        {
            if (!overwrite && Exists(filename))
            {
                return 0;
            }

            return Insert(filename, buffer, contentType, contentLength);
        }

        public int Update(string filename, byte[] buffer, string contentType, long contentLength)
        {
            return Insert(filename, buffer, contentType, contentLength);
        }

    }
}
